package com.jsmtr.rms.service.impl;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.entity.*;
import com.jsmtr.rms.service.IDoResumeService;
import com.jsmtr.rms.utils.BeanTools;
import com.jsmtr.rms.utils.StringTools;
import org.springframework.transaction.annotation.Transactional;

@Service
public class DoResumeService implements IDoResumeService {

    @Resource
    private DaoService dao;

    public void setDao(DaoService dao) {
        this.dao = dao;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<String, Object> getResumes(int page, int perpage, Map<String, Object> param, DUserResume resume) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("  from DUserResume dur where 1=1");
        StringBuffer countHQL = new StringBuffer("select count(*) from DUserResume dur where 1=1");
        StringBuffer condition = new StringBuffer();

        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");

        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto");
        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));
        condition.append(" order by dur.code");

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString(), _map)).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<DUserResume> list = dao.findLimit(queryHQL.toString(), _map, perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("list", list);
        return map;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DUserResume> getResumes(String[] ids) throws Exception {
        return dao.find("from DUserResume where id in(:idlist)", ids);
    }

    @Override
    public List<DUserResume> getResumes(String pid) throws Exception {
        @SuppressWarnings("unchecked")
        List<DUserResume> resumes = dao.find("from DUserResume where proid='" + pid + "'");
        return resumes;
    }

    @Override
    public DUserResume getDUserResume(String id) throws Exception {
        return (DUserResume) dao.findUnique("from DUserResume where id='" + id + "'");
    }

    @Override
    public UserResume getUserResume(String cardno) throws Exception {
        return (UserResume) dao.findUnique("from UserResume where cardno='" + cardno + "' ");
    }

    @Override
    public UserResume gUserResume(String id) throws Exception {
        return (UserResume) dao.findUnique("from UserResume where id='" + id + "'");
    }

    @Override
    public NZResume getNZResume(String id) throws Exception {
        return (NZResume) dao.findUnique("from NZResume where id='" + id + "'");
    }

    @Override
    public UserResume gUserResumeByUid(String uid) throws Exception {
        return (UserResume) dao.findUnique("from UserResume where userid='" + uid + "'");
    }

    //教育背景
    @SuppressWarnings("unchecked")
    @Override
    public List<DUserEducation> getUserEducation(String rid) throws Exception {
        return dao.find("from DUserEducation where rid='" + rid + "' order by educationcode asc ");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserEducation> getUserEducation1(String rid) throws Exception {
        return dao.find("from UserEducation where rid='" + rid + "' order by educationcode asc ");
    }

    //工作经验
    @SuppressWarnings("unchecked")
    @Override
    public List<DUserWorkExperience> getUserWorkExperience(String rid) throws Exception {
        return dao.find("from DUserWorkExperience where rid='" + rid + "' order by endtime desc");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserWorkExperience> getUserWorkExperience1(String rid) throws Exception {
        return dao.find("from UserWorkExperience where rid='" + rid + "'");
    }

    //培训经历
    @SuppressWarnings("unchecked")
    @Override
    public List<DUserTraining> getUserTraining(String rid) throws Exception {
        return dao.find("from DUserTraining where rid='" + rid + "'");
    }

    @Override
    public String getConfig(String code) throws Exception {
        BaseConfig config = (BaseConfig) dao.findUnique("from BaseConfig where code='" + code + "'");
        return config.getCont();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserTraining> getUserTraining1(String rid) throws Exception {
        return dao.find("from UserTraining where rid='" + rid + "'");
    }

    //获奖信息
    @SuppressWarnings("unchecked")
    @Override
    public List<DUserRewardPunishment> getUserRewardPunishment(String rid) throws Exception {
        return dao.find("from DUserRewardPunishment where rid='" + rid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserRewardPunishment> getUserRewardPunishment1(String rid) throws Exception {
        return dao.find("from UserRewardPunishment where rid='" + rid + "'");
    }

    //家庭成员
    @SuppressWarnings("unchecked")
    @Override
    public List<DUserFamily> getUserFamily(String rid) throws Exception {
        return dao.find("from DUserFamily where rid='" + rid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<UserFamily> getUserFamily1(String rid) throws Exception {
        return dao.find("from UserFamily where rid='" + rid + "'");
    }

    @Override
    public List<UserPaper> getUserPaper(String rid) throws Exception {
        return dao.find("from UserPaper where rid='" + rid + "'");
    }

    @Override
    public List<UserAppend> getUserAppend(String cardno) throws Exception {
        return  dao.find("from UserAppend where cardno='" + cardno + "' and state = 1");
        //return dao.find("from UserAppend where cardno=? and state = 1", new String[]{cardno});
    }

    @Override
    public List<UserAppend> findUserAppendByCardNo(String cardno) throws Exception {
        return  dao.find("from UserAppend where cardno=:cardno and state = 1","cardno",cardno);

    }
    @Override
    public UserAppend getUserAppendByType(String cardno,String type) throws Exception {
        return (UserAppend) dao.findUnique("from UserAppend where cardno='" + cardno + "' and type = '"+ type + "' and state = 1");
    }

    @Override
    public List<DUserMajor> getUserMajor(String rid) throws Exception {
        return dao.find("from DUserMajor where rid='" + rid + "'");
    }

    @Override
    public List<UserMajor> getUserMajor1(String rid) throws Exception {
        return dao.find("from UserMajor where rid='" + rid + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public boolean AllowSendResume(String pid, String userid) throws Exception {
        List<DUserResume> durs = dao.find("from DUserResume where proid='" + pid + "' and userid='" + userid + "'");
        if (durs.size() == 0) {
            return true;
        }
        int WFQCount = 0;
        for (DUserResume dur : durs) {
            if (dur.getFqstate().equals("未放弃")) {
                WFQCount++;
            }
        }
        if (WFQCount == 0){
            return true;
        }


        return false;
    }


    @Override
    public ProjectInfoFlow getFirstFlow(String pid) throws Exception {
        return (ProjectInfoFlow) dao.findFirst("from ProjectInfoFlow where pid='" + pid + "' order by forder");
    }

    @Override
    public ProjectInfoJobs getProjectJobs(String id) throws Exception {
        return (ProjectInfoJobs) dao.findUnique("from ProjectInfoJobs where id='" + id + "'");
    }

    @Override
    public BaseDepartment getBaseDept(String name) throws Exception {
        return (BaseDepartment) dao.findUnique("from BaseDepartment where name='" + name + "'");
    }

    @Override
    public BaseConfig getBaseConfig(String code) throws Exception {
        return (BaseConfig) dao.findUnique("from BaseConfig where code='" + code + "'");
    }

    @Override
    public BaseJob getBaseJob(String deptcode,String name) throws Exception {
        return (BaseJob) dao.findUnique("from BaseJob where deptcode='"+deptcode+"' and name='" + name + "'");
    }

    @Override
    public DUserResume getDResume(String userid, String proid) throws Exception {
        return (DUserResume) dao.findUnique("from DUserResume where userid='" + userid + "' and proid='" + proid + "' and fqstate='未放弃'");
    }


    @Override
    public HRZZUser getUser(String pid, String code) throws Exception {
        return (HRZZUser) dao.findUnique("from HRZZUser where pid='" + pid + "' and code='" + code + "'");
    }

    @Override
    public List<DUserResume> getDResumeByCardNo(String cardNo, String proid) throws Exception {
        return dao.find("from DUserResume where cardno='" + cardNo + "' and proid='" + proid + "' and fqstate='未放弃'");
    }

    @Override
    public List<DUserResume> getDResumeByCardNoAllState(String cardNo, String proid) throws Exception {
        return dao.find("from DUserResume where cardno='" + cardNo + "' and proid='" + proid + "' order by adddate desc");
    }

    @Override
    public List<DUserResume> getDResumeByJob(String jname,String proid) throws Exception {
        return dao.find("from DUserResume where jname='" + jname + "' and proid='" + proid + "' and fqstate='未放弃' order by adddate desc");
    }

    @Override
    public List<Map<String, Object>> getDResumeByCardNo(String cardNo) throws Exception {
        String sql=" select r.*,\n" +
                "       to_char(r.addDate,'yyyy-MM-dd HH24:mi:ss') jtime,\n" +
                "       (select f.nickname from project_info_flow f where f.id = r.fid) lastflow,\n" +
                "       ms.result msstate,\n" +
                "       ms.remark msremark,\n" +
                "       ms.op7 msfb,\n" +
                "       bs.result bsstate,\n" +
                "       bs.remark bsremark,\n" +
                "       bs.op6 bsfb,\n" +
                "       dop.result dopstate,\n" +
                "       dop.bz dopremark,\n" +
                "       dop.op8 dopfb,\n" +
                "        to_char(r.fqdate,'YYYY-MM-DD HH24:mi:ss') fqtime,\n"+
                "       (select f.nickname from project_info_flow f where f.id = r.fqfid) fqflow \n" +
                "  from duserresume r, doms_user ms, dobs_user bs, dooperation_user dop\n" +
                " where r.id = ms.userid(+)\n" +
                "   and r.id = bs.userid(+)\n" +
                "   and r.id = dop.userid(+)\n" +
                "   and r.cardno=:cardno \n" +
                "   order by r.addDate desc \n"
                ;

        Map params=new HashMap();
        params.put("cardno",cardNo);
        return dao.executeSQLQueryToMap(sql,params);
    }

    @Override
    public List<Map<String,Object>> getDResumeFlowsInfo(String userid) throws Exception{
        String sql= "select uflows.uflow flowid,\n" +
                "       pf.fid  flownumber,\n" +
                "       pf.nickname  flownickname,\n" +
                "       pf.fname  flowname,\n" +
                "       pf.webname  flowwebname,\n" +
                "       pf.forder   floworder,\n" +
                "       pf.state     flowstate,\n" +
                "       pf.starttime flowstartime,\n" +
                "       pf.endtime   flowendtime,\n" +
                "       pf.remark    flowremark,\n" +
                "       pf.display flowdisplay,\n" +
                "       duser.fqfid fqfid,\n" +
                "       duser.fqstate fqstate,\n" +
                "       to_char(duser.fqdate,'YYYY-MM-DD HH24:mi:ss') fqdate,\n" +
                "   (select   ubz.content   from  duserremark ubz where uflows.userid=ubz.rid(+)  and pf.pid = ubz.pid(+) and pf.id = ubz.fid(+) ) userremark,\n" +
                "       case  when ( pf.nickname like '%简历筛选%' or pf.fname like '%简历筛选%') then (select '{\"state\":\"'||u.state||'\",\"fbzt\":\"'||u.op1||'\",\"pointdate\":\"'||to_char(u.editdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.editusername||'\",\"remark\":\"'||u.sxbz||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                "         when (pf.nickname like '%笔试%' or pf.fname like '%笔试%') then (select '{\"address\":\"'||bu.address||'\",\"name\":\"'||bu.kcname||'\",\"msg\":\"'||bu.notice||'\",\"ticketnum\":\"'||bu.ticketnum||'\",\"time\":\"'||bu.starttime||'\",\"state\":\"'||bu.result||'\",\"fbzt\":\"'||bu.op6||'\",\"point\":\"'||bu.point||'\",\"pointdate\":\"'||to_char(bu.pointdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||bu.pointuser||'\",\"remark\":\"'||bu.remark||'\"}' from dobs_user bu  where bu.userid=uflows.userid)\n" +
                "           when (pf.nickname like '%资料审核%' or pf.fname like '%资料审核%') then (select '{\"state\":\"'||u.zlstate||'\",\"msg\":\"'||u.zlmsg||'\",\"msgcont\":\"'||u.zlmsgcont||'\",\"pointdate\":\"'||to_char(u.zldate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.zluname||'\",\"fbzt\":\"'||u.op2||'\",\"remark\":\"'||u.zlremark||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                "             when (pf.nickname like '%面试%' or pf.fname like '%面试%') then (select '{\"address\":\"'||mu.address||'\",\"msg\":\"'||mu.notice||'\",\"isconfirm\":\"'||mc.isconfirm||'\",\"confirmtime\":\"'||mc.confirmtime||'\",\"time\":\"'||mu.starttime||'\",\"state\":\"'||mu.result||'\",\"fbzt\":\"'||mu.op7||'\",\"point\":\"'||mu.point||'\",\"pointdate\":\"'||to_char(mu.pointdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||mu.pointuser||'\",\"remark\":\"'||mu.remark||'\"}' from doms_user mu,doms_confirm mc  where mu.userid=uflows.userid and mu.id=mc.msuserid(+))\n" +
                "               when (pf.nickname like '%实作%' or pf.fname like '%实作%') then (select '{\"address\":\"'||ou.address||'\",\"name\":\"'||ou.cont||'\",\"msg\":\"'||ou.notice||'\",\"time\":\"'||ou.starttime||'\",\"state\":\"'||ou.result||'\",\"fbzt\":\"'||ou.op8||'\",\"point\":\"'||ou.point||'\",\"pointdate\":\"'||to_char(ou.pointdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||ou.pointuser||'\",\"remark\":\"'||ou.bz||'\"}' from dooperation_user ou  where ou.userid=uflows.userid)\n" +
                "                 when (pf.nickname like '%体检%' or pf.fname like '%体检%') then (select  '{\"address\":\"'||u.tjaddress||'\",\"msg\":\"'||u.tjmsg||'\",\"tjlxr\":\"'||u.tjlxr||'\",\"tjlxdh\":\"'||u.tjlxdh||'\",\"time\":\"'||u.tjtime||'\",\"state\":\"'||u.tjstate||'\",\"fbzt\":\"'||u.op4||'\",\"point\":\"\",\"pointdate\":\"'||to_char(u.tjdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.tjuname||'\",\"remark\":\"'||u.tjremark||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                "                   when (pf.nickname like '%背景调查%' or pf.fname like '%背景调查%' or pf.nickname like '%政审%' )  then (select  '{\"state\":\"'||u.zsstate||'\",\"fbzt\":\"'||u.op3||'\",\"pointdate\":\"'||to_char(u.zsdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.zsuname||'\",\"remark\":\"'||u.zsremark||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                "                     when (pf.nickname like '%实习%' or pf.fname like '%实习%') then (select  '{\"state\":\"'||u.sxstate||'\",\"fbzt\":\"'||u.op7||'\",\"pointdate\":\"'||to_char(u.sxdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.sxuname||'\",\"remark\":\"'||u.sxremark||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                "                        when (pf.nickname like '%录用%' or pf.fname like '%录用%') then (select  '{\"state\":\"'||u.bdstate||'\",\"fbzt\":\"'||u.op5||'\",\"address\":\"'||u.bdaddress||'\",\"pointdate\":\"'||to_char(u.bddate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.bduname||'\",\"remark\":\"'||u.bdremark||'\"}' from duserresume u  where u.id=uflows.userid) \n"+
                " when (pf.nickname like '%签约%' or pf.fname like '%签约%') then (select  '{\"state\":\"'||u.qystate||'\",\"fbzt\":\"'||u.op9||'\",\"remark\":\"'||u.qyremark||'\"}' from duserresume u  where u.id=uflows.userid) \n"+
                "         end as resumecontent\n" +
                "  from project_info_flow pf,\n" +
                "      duserresume duser ,\n" +
                "       (SELECT REGEXP_SUBSTR(pt.tpath, '[^,]+', 1, rownum) uflow,pt.id userid\n" +
                "          from (select regexp_replace(substr(r.path, 2, length(r.path) - 2),\n" +
                "                                      '/',\n" +
                "                                      ',') tpath,r.id\n" +
                "                  from duserresume r\n" +
                "                 where r.id =:userid ) pt\n" +
                "        connect by rownum <= LENGTH(pt.tpath) -\n" +
                "                   LENGTH(regexp_replace(pt.tpath, ',', '')) + 1) uflows\n" +
                " where pf.id=uflows.uflow(+)\n" +
                "   and duser.id=uflows.userid\n" +
                "   order by pf.forder asc";
        Map params=new HashMap();
        params.put("userid",userid);
        return dao.executeSQLQueryToMap(sql,params);
    }

    @Override
    public List<Map<String,Object>> getZZUserDResumeFlowsInfo(String userid) throws Exception{
        String sql=
                "select uflows.uflow flowid,\n" +
                        "       pf.fid  flownumber,\n" +
                        "       pf.nickname  flownickname,\n" +
                        "       pf.fname  flowname,\n" +
                        "       pf.webname  flowwebname,\n" +
                        "       pf.forder   floworder,\n" +
                        "       pf.state     flowstate,\n" +
                        "       pf.starttime flowstartime,\n" +
                        "       pf.endtime   flowendtime,\n" +
                        "       pf.remark    flowremark,\n" +
                        "       pf.display flowdisplay,\n" +
                        "       duser.fqfid fqfid,\n" +
                        "       duser.fqstate fqstate,\n" +
                        "       to_char(duser.fqdate,'YYYY-MM-DD HH24:mi:ss') fqdate,\n" +
                        "   (select   ubz.content   from  duserremark ubz where uflows.userid=ubz.rid(+)  and pf.pid = ubz.pid(+) and pf.id = ubz.fid(+) ) userremark,\n" +
                        "       case  when (pf.nickname like '%简历筛选%' or pf.fname like '%简历筛选%' or pf.nickname like '%毕业生推荐%') then (select '{\"state\":\"'||u.state||'\",\"fbzt\":\"'||u.op1||'\",\"pointdate\":\"'||to_char(u.rdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.rusername||'\",\"remark\":\"'||u.rmarks||'\"}' from hr_zzuser u  where u.id=uflows.userid)\n" +
                        "         when (pf.nickname like '%笔试%' or pf.fname like '%笔试%') then (select '{\"address\":\"'||bu.address||'\",\"name\":\"'||bu.kcname||'\",\"msg\":\"'||bu.notice||'\",\"ticketnum\":\"'||bu.ticketnum||'\",\"time\":\"'||bu.starttime||'\",\"state\":\"'||bu.result||'\",\"fbzt\":\"'||bu.op6||'\",\"point\":\"'||bu.point||'\",\"pointdate\":\"'||to_char(bu.pointdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||bu.pointuser||'\",\"remark\":\"'||bu.remark||'\"}' from dobs_user bu  where bu.userid=uflows.userid)\n" +
                        "           when (pf.nickname like '%资料审核%' or pf.fname like '%资料审核%') then (select '{\"state\":\"'||u.zlstate||'\",\"msg\":\"'||u.zlmsg||'\",\"msgcont\":\"'||u.zlmsgcont||'\",\"pointdate\":\"'||to_char(u.zldate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.zluname||'\",\"fbzt\":\"'||u.op2||'\",\"remark\":\"'||u.zlremark||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                        "             when (pf.nickname like '%面试%' or pf.fname like '%面试%') then (select '{\"address\":\"'||mu.address||'\",\"msg\":\"'||mu.notice||'\",\"isconfirm\":\"'||mc.isconfirm||'\",\"confirmtime\":\"'||mc.confirmtime||'\",\"time\":\"'||mu.starttime||'\",\"state\":\"'||mu.result||'\",\"fbzt\":\"'||mu.op7||'\",\"point\":\"'||mu.point||'\",\"pointdate\":\"'||to_char(mu.pointdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||mu.pointuser||'\",\"remark\":\"'||mu.remark||'\"}' from doms_user mu,doms_confirm mc  where mu.userid=uflows.userid and mu.id=mc.msuserid(+))\n" +
                        "               when (pf.nickname like '%实作%' or pf.fname like '%实作%') then (select '{\"address\":\"'||ou.address||'\",\"name\":\"'||ou.cont||'\",\"msg\":\"'||ou.notice||'\",\"time\":\"'||ou.starttime||'\",\"state\":\"'||ou.result||'\",\"fbzt\":\"'||ou.op8||'\",\"point\":\"'||ou.point||'\",\"pointdate\":\"'||to_char(ou.pointdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||ou.pointuser||'\",\"remark\":\"'||ou.bz||'\"}' from dooperation_user ou  where ou.userid=uflows.userid)\n" +
                        "                 when (pf.nickname like '%体检%' or pf.fname like '%体检%') then (select  '{\"address\":\"'||u.tjaddress||'\",\"msg\":\"'||u.tjmsg||'\",\"tjlxr\":\"'||u.tjlxr||'\",\"tjlxdh\":\"'||u.tjlxdh||'\",\"time\":\"'||u.tjtime||'\",\"state\":\"'||u.tjstate||'\",\"fbzt\":\"'||u.op4||'\",\"point\":\"\",\"pointdate\":\"'||to_char(u.tjdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.tjuname||'\",\"remark\":\"'||u.tjremark||'\"}' from hr_zzuser u  where u.id=uflows.userid)\n" +
                        "                   when (pf.nickname like '%背景调查%' or pf.fname like '%背景调查%' or pf.nickname like '%政审%')  then (select  '{\"state\":\"'||u.zsstate||'\",\"fbzt\":\"'||u.op3||'\",\"pointdate\":\"'||to_char(u.zsdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.zsuname||'\",\"remark\":\"'||u.zsremark||'\"}' from hr_zzuser u  where u.id=uflows.userid)\n" +
                        "                     when (pf.nickname like '%实习%' or pf.fname like '%实习%') then (select  '{\"state\":\"'||u.sxstate||'\",\"fbzt\":\"'||u.op7||'\",\"pointdate\":\"'||to_char(u.sxdate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.sxuname||'\",\"remark\":\"'||u.sxremark||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                        "                        when (pf.nickname like '%录用%' or pf.fname like '%录用%') then (select  '{\"state\":\"'||u.bdstate||'\",\"fbzt\":\"'||u.op5||'\",\"address\":\"'||u.bdaddress||'\",\"pointdate\":\"'||to_char(u.bddate,'yyyy-MM-dd hh24:mi:ss')||'\",\"pointuser\":\"'||u.bduname||'\",\"remark\":\"'||u.bdremark||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                        "                           when (pf.nickname like '%签约%' or pf.fname like '%签约%') then (select  '{\"state\":\"'||u.qystate||'\",\"fbzt\":\"'||u.op9||'\",\"remark\":\"'||u.qyremark||'\"}' from duserresume u  where u.id=uflows.userid)\n" +
                        "         end as resumecontent\n" +
                        "  from project_info_flow pf,\n" +
                        "      duserresume duser , hr_zzuser zzuser ,\n" +
                        "       (SELECT REGEXP_SUBSTR(pt.tpath, '[^,]+', 1, rownum) uflow,pt.id userid\n" +
                        "          from (select regexp_replace(substr(r.path, 2, length(r.path) - 2),\n" +
                        "                                      '/',\n" +
                        "                                      ',') tpath,r.id\n" +
                        "                  from hr_zzuser r\n" +
                        "                 where r.id =:userid ) pt\n" +
                        "        connect by rownum <= LENGTH(pt.tpath) -\n" +
                        "                   LENGTH(regexp_replace(pt.tpath, ',', '')) + 1) uflows\n" +
                        " where pf.id=uflows.uflow(+)\n" +
                        "   and duser.id=uflows.userid\n" +
                        "   and duser.id=zzuser.id\n" +
                        "   order by pf.forder asc";

        Map params=new HashMap();
        params.put("userid",userid);
        return dao.executeSQLQueryToMap(sql,params);
    }

    @Override
    public Map<String, Object> getResumesMap(int page, int perpage, Map<String, Object> param, DUserResume resume) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
       /* StringBuffer queryHQL2 = new StringBuffer(" select dur.*,(select count(1) from duserresume r where r.cardno = dur.cardno) cardcount,dr.content as mcontent,dr.id as mid,hu.logintime from duserresume dur, (select * from  duserremark  where fid like '%"+resume.getPath().replaceAll("/","")+"%') dr,hr_user hu where 1=1 and  dur.id = dr.rid(+)  and dur.proid = dr.pid(+) and dur.userid = hu.id ");
        StringBuffer countHQL2 = new StringBuffer(" select count(*)  from duserresume dur, (select * from  duserremark  where fid like '%"+resume.getPath().replaceAll("/","")+"%') dr,hr_user hu  where 1=1 and  dur.id = dr.rid(+)  and dur.proid = dr.pid(+) and dur.userid = hu.id(+) ");*/

        StringBuffer queryHQL=new StringBuffer();
        queryHQL.append(
                "select  dur.id,\n" +
                        "       to_char(dur.adddate,'yyyy-MM-dd hh24:mi') as adddate,\n" +
                        "       dur.attendancetime,\n" +
                        "       dur.bdaddress,\n" +
                        "       dur.bddate,\n" +
                        "       dur.bdlxdh,\n" +
                        "       dur.bdlxr,\n" +
                        "       dur.bdmsg,\n" +
                        "       dur.bdremark,\n" +
                        "       dur.bdstate,\n" +
                        "       dur.bdtime,\n" +
                        "       dur.bduid,\n" +
                        "       dur.bduname,\n" +
                        "       dur.birthday,\n" +
                        "       dur.cardaddress,\n" +
                        "       dur.cardno,\n" +
                        "       dur.code,\n" +
                        "       dur.distribution,\n" +
                        "       dur.editdate,\n" +
                        "       dur.editusername,\n" +
                        "       dur.edituserid,\n" +
                        "       wk.companyname wkcompanyname,\n" +
                        "       wk.department wkdepartment,\n" +
                        "       wk.position wkposition,\n" +
                        "       wk.endtime wkendtime,\n"+
                        "        mxdusereducation.educationcode as education,\n" +
                        "        mxdusereducation.educationname as educationname,\n" +
                        "       dur.email,\n" +
                        "       dur.expectedsalaryamount,\n" +
                        "       dur.fid,\n" +
                        "       dur.fqdate,\n" +
                        "       dur.fqfid,\n" +
                        "       dur.fqstate,\n" +
                        "       mxdusereducation.endtime as graduationtime,\n" +
                        "       dur.head,\n" +
                        "       dur.height,\n" +
                        "       dur.hkcode,\n" +
                        "       dur.hkname,\n" +
                        "       dur.hometel,\n" +
                        "       dur.hukouaddress,\n" +
                        "       dur.jg,\n" +
                        "       dur.jid,\n" +
                        "       dur.jname,\n" +
                        "       jobs.jobdepart,\n" +
                        "       mxdusereducation.majorname as majorname,\n" +
                        "       dur.mcode,\n" +
                        "       dur.mname,\n" +
                        "       dur.mzcode,\n" +
                        "       dur.mzname,\n" +
                        "       dur.name,\n" +
                        "       dur.nowaddress,\n" +
                        "       dur.othcode,\n" +
                        "       dur.othdate,\n" +
                        "       dur.othlevel,\n" +
                        "       dur.othname,\n" +
                        "       dur.othorg,\n" +
                        "       dur.othtypecode,\n" +
                        "       dur.othtypename,\n" +
                        "       dur.othzy,\n" +
                        "       dur.path,\n" +
                        "       dur.pid,\n" +
                        "       dur.pname,\n" +
                        "       dur.postcompany,\n" +
                        "       dur.postgettime,\n" +
                        "       dur.postlevelcode,\n" +
                        "       dur.postlevelname,\n" +
                        "       dur.postspecialty,\n" +
                        "       dur.posttypecode,\n" +
                        "       dur.posttypename,\n" +
                        "       dur.presentsalaryamount,\n" +
                        "       dur.proid,\n" +
                        "       dur.proname,\n" +
                        "       dur.qlgettime,\n" +
                        "       dur.qllevelcode,\n" +
                        "       dur.qllevelname,\n" +
                        "       dur.qllevelpid,\n" +
                        "       dur.qlorg,\n" +
                        "       dur.qltype,\n" +
                        "       dur.sexcode,\n" +
                        "       dur.sexname,\n" +
                        "       dur.state,\n" +
                        "       dur.sxbz,\n" +
                        "       dur.tel,\n" +
                        "       dur.tjaddress,\n" +
                        "       dur.tjdate,\n" +
                        "       dur.tjlxdh,\n" +
                        "       dur.tjlxr,\n" +
                        "       dur.tjmsg,\n" +
                        "       dur.tjremark,\n" +
                        "       dur.tjstate,\n" +
                        "       dur.tjtime,\n" +
                        "       dur.tjuid,\n" +
                        "       dur.tjuname,\n" +
                        "       mxdusereducation.schoolname as university,\n" +
                        "       dur.userid,\n" +
                        "       dur.workexperience,\n" +
                        "       dur.zldate,\n" +
                        "       dur.zldelay,\n" +
                        "       dur.zljstime,\n" +
                        "       dur.zlkstime,\n" +
                        "       dur.zlmsg,\n" +
                        "       dur.zlmsgcont,\n" +
                        "       dur.zlremark,\n" +
                        "       dur.zlstate,\n" +
                        "       dur.zluid,\n" +
                        "       dur.zluname,\n" +
                        "       dur.zsdate,\n" +
                        "       dur.zsremark,\n" +
                        "       dur.zsstate,\n" +
                        "       dur.zsuid,\n" +
                        "       dur.zsuname,\n" +
                        "       dur.zzmmcode,\n" +
                        "       dur.zzmmname,\n" +
                        "       dur.op1,\n" +
                        "       dur.op2,\n" +
                        "       dur.op3,\n" +
                        "       dur.op4,\n" +
                        "       dur.op5,\n" +
                        "       dur.rcode,\n" +
                        "       dur.region,\n" +
                        "       dur.gwdate,\n" +
                        "       dur.gwuid,\n" +
                        "       dur.gwuname,\n" +
                        "       dur.gwstate,\n" +
                        "       dur.op6,\n" +
                        "       dur.rfid,\n" +
                        "       dur.orid,\n" +
                        "       dur.nrid,\n" +
                        "       dur.njname,\n" +
                        "       dur.changedate,\n" +
                        "       dur.qllevelpname,\n" +
                        "       dur.qllevelscode,\n" +
                        "       dur.qllevelsname,\n" +
                        "       dur.rcount,\n" +
                        "       dur.op7,\n" +
                        "       dur.sxstate,\n" +
                        "       dur.sxremark,\n" +
                        "       dur.sxuid,\n" +
                        "       dur.sxuname,\n" +
                        "       dur.sxdate,\n" +
                        "       dur.op9,\n" +
                        "       dur.qystate,\n" +
                        "       dur.qyremark,\n" +
                        "       dur.qyuid,\n" +
                        "       dur.qyuname,\n" +
                        "       dur.qydate,\n" +
                        "       cardnototal.cardcount,\n" +
                        "       dr.content as mcontent,\n" +
                        "       dr.id as mid,\n" +
                        "       hu.logintime\n" +
                        "  from duserresume dur,\n" +
                        "(select count(1) cardcount, r.cardno\n" +
                        "                          from duserresume r\n" +
                        "                         group by r.cardno) cardnototal,\n"+
                        "( select de2.* from view_zjeducation de2) mxdusereducation,\n"+
                        "( select mk.* from VIEW_MAXWORK mk) wk,\n"+

                        "       (select *\n" +
                        "          from duserremark\n" +
                        "         where fid like '%"+resume.getPath().replaceAll("/","")+"%' ) dr,\n" +
                        "  project_info_jobs jobs, \n"+
                        "       hr_user hu\n" +
                        " where 1 = 1\n" +
                        "   and dur.id = dr.rid(+)\n" +
                        "   and dur.jid=jobs.id(+) \n"+
                        "   and dur.proid = dr.pid(+)\n" +
                        "   and dur.id= wk.rid(+)\n"+
                        "   and dur.cardno = cardnototal.cardno(+) \n"+
                        "   and dur.id=mxdusereducation.rid(+)\n" +
                        "   and dur.userid = hu.id(+)\n"
        );

        StringBuffer countHQL = new StringBuffer();
        countHQL.append("select count(1)\n" +
                "  from duserresume dur,\n" +
                " (select count(1) cardcount, r.cardno\n" +
                "                          from duserresume r\n" +
                "                         group by r.cardno) cardnototal,\n"+
                "( select de2.* from view_zjeducation de2) mxdusereducation,\n"+
                "(select mk.* from VIEW_MAXWORK mk) wk,\n"+
                "       (select *\n" +
                "          from duserremark\n" +
                "         where fid like '%"+resume.getPath().replaceAll("/","")+"%' ) dr,\n" +
                "  project_info_jobs jobs, \n"+
                "       hr_user hu\n" +
                " where 1 = 1\n" +
                "   and dur.id = dr.rid(+)\n" +
                "   and dur.proid = dr.pid(+)\n" +
                "   and dur.jid=jobs.id(+) \n"+
                "   and dur.id= wk.rid(+)\n"+
                "   and dur.cardno = cardnototal.cardno(+) \n"+
                "   and dur.id=mxdusereducation.rid(+)\n" +
                "   and dur.userid = hu.id(+)\n" );

        StringBuffer condition = new StringBuffer();

        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");
        Object usercontent = param.get("usercontent");
        Object resultediter = param.get("resultediter");
        Object wkcompanyname=param.get("wkcompanyname");
        Object wkdepartment =param.get("wkdepartment");
        Object wkposition= param.get("wkposition");
        Object jobdepart=param.get("jobdepart");
        /* Object adddate=param.get("adddate");*/



        Map<String, Object> _map = new HashMap<String, Object>();
        Object op1= param.get("op1");

        if (StringTools.notEmpty(op1)) {
            if (op1.equals("未公布")){
                condition.append(" and (dur.op1 ='未公布' or dur.op1 is null )");
            }else if(op1.equals("已公布")){
                condition.append(" and dur.op1 ='已公布' ");
            }
            resume.setOp1("");
        }



        Object bdremark=param.get("bdremark");
        if (StringTools.notEmpty(bdremark)) {

            if (bdremark.equals("已发送")){

                condition.append(" and dur.bdremark like '%\"error_code\":0%' ");
            }else if(bdremark.equals("发送失败")){
                condition.append(" and dur.bdremark like '%\"error_code\":1%' ");
            }else if(bdremark.equals("未发送")){
                condition.append(" and dur.bdremark is null ");
            }
            resume.setBdremark("");
        }

        Object adddate=param.get("adddate");
        if (StringTools.notEmpty(adddate)) {
            _map.put("adddate", adddate);
            condition.append(" and adddate like '%'||:adddate||'%'");
        }

        Object adddatefrom=param.get("adddatefrom");
        if (StringTools.notEmpty(adddatefrom)) {
           /* cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);*/
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            _map.put("adddatefrom", sdf.parse(adddatefrom.toString().trim()));
            //_map.put("adddatefrom", adddatefrom.toString().trim());
            condition.append(" and adddate >= :adddatefrom ");
        }

        Object adddateto=param.get("adddateto");
        if (StringTools.notEmpty(adddateto)) {
            /*cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);*/
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            _map.put("adddateto", sdf.parse(adddateto.toString().trim()));
            //_map.put("adddateto", adddatefrom.toString().trim());
            condition.append(" and adddate <= :adddateto ");
        }


        if (StringTools.notEmpty(jobdepart)) {
            _map.put("jobdepart", jobdepart);
            condition.append(" and jobs.jobdepart like '%'||:jobdepart||'%'");
        }
        if (StringTools.notEmpty(wkcompanyname)) {
            _map.put("wkcompanyname", wkcompanyname);
            condition.append(" and wk.companyname like '%'||:wkcompanyname||'%'");
        }
        if (StringTools.notEmpty(wkdepartment)) {
            _map.put("wkdepartment", wkdepartment);
            condition.append(" and wk.department like '%'||:wkdepartment||'%'");
        }
        if (StringTools.notEmpty(wkposition)) {
            _map.put("wkposition", wkposition);
            condition.append(" and wk.position like '%'||:wkposition||'%'");
        }

        if (StringTools.notEmpty(wkposition)) {
            _map.put("wkposition", wkposition);
            condition.append(" and wk.position like '%'||:wkposition||'%'");
        }

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }

        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto");
        }

        if (StringTools.notEmpty(usercontent)){
            if (usercontent.toString().equalsIgnoreCase("已记录")){
                condition.append(" and  dr.content is not null ");
            }else  if (usercontent.toString().equalsIgnoreCase("未记录")){
                condition.append(" and  dr.content is  null ");
            }
        }

        Object ischangejob = param.get("ischangejob");
        if (StringTools.notEmpty(ischangejob)){
            if (ischangejob.toString().equalsIgnoreCase("是")){
                condition.append(" and  dur.njname is not null ");
            }else  if (ischangejob.toString().equalsIgnoreCase("否")){
                condition.append(" and  dur.njname is  null ");
            }

        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));
        //condition.append(" order by dur.code ");

        queryHQL.append(condition);
        countHQL.append(condition);
        //double count = ((Long) dao.executeSQL(countHQL.toString(), _map)).doubleValue();
        BigDecimal count= (BigDecimal) dao.executeSQLQuery(countHQL.toString(),_map).get(0);
        double pageCount = Math.ceil(count.doubleValue() / (double) perpage);

        queryHQL.append(" order  by dur.name asc ");
        List<Map<String, Object>> list = dao.executeSQLQueryMapPage(queryHQL.toString(), _map, perpage * (page - 1), perpage);
        map.put("count", count.doubleValue());
        map.put("pageCount", pageCount);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> getMSResumesMap(int page, int perpage, Map<String, Object> param, DUserResume resume) throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();


        StringBuffer queryHQL = new StringBuffer(" select dur.*,hu.logintime,cardnototal.cardcount, ms.id msid,ms.address msaddress,ms.starttime msstarttime,ms.point mspoint,ms.result msresult,ms.op7 msop7,(case ms.notice  when 0 then '未发通知' when 1 then '已发通知' end) as msnotice, ms.remark  msremark, ms.scoringrules msscoringrules,  mc.isconfirm msisconfirm, mc.confirmtime msconfirmtime, mc.id mscomfirmid,dr.content as mcontent,dr.id as mid from duserresume dur,(select count(1) cardcount, r.cardno from duserresume r group by r.cardno) cardnototal,doms_user  ms, doms_confirm mc, (select * from  duserremark  where fid like '%"+resume.getPath().replaceAll("/","")+"%') dr,hr_user hu where 1=1 and  dur.id = dr.rid(+)  and dur.proid = dr.pid(+) and dur.id = ms.userid(+)  and ms.id = mc.msuserid(+) and dur.userid = hu.id and dur.cardno = cardnototal.cardno(+) ");
        StringBuffer countHQL = new StringBuffer(" select count(1) from duserresume dur,(select count(1) cardcount, r.cardno from duserresume r group by r.cardno) cardnototal,doms_user ms, doms_confirm mc, (select * from  duserremark  where fid like '%"+resume.getPath().replaceAll("/","")+"%') dr,hr_user hu where 1=1 and  dur.id = dr.rid(+)  and dur.proid = dr.pid(+) and dur.id = ms.userid(+)  and ms.id = mc.msuserid(+) and dur.userid = hu.id and dur.cardno = cardnototal.cardno(+) ");
        StringBuffer condition = new StringBuffer();

        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");


        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto");
        }

        Object usercontent = param.get("usercontent");
        if (StringTools.notEmpty(usercontent)){
            if (usercontent.toString().equalsIgnoreCase("已记录")){
                condition.append(" and  dr.content is not null ");
            }else  if (usercontent.toString().equalsIgnoreCase("未记录")){
                condition.append(" and  dr.content is  null ");
            }

        }

        Object ischangejob = param.get("ischangejob");
        if (StringTools.notEmpty(ischangejob)){
            if (ischangejob.toString().equalsIgnoreCase("是")){
                condition.append(" and  dur.njname is not null ");
            }else  if (ischangejob.toString().equalsIgnoreCase("否")){
                condition.append(" and  dur.njname is  null ");
            }

        }

        if (StringTools.notEmpty(param.get("msplan"))) {
            String msaddress=param.get("msplan").toString();
            if(msaddress.equalsIgnoreCase("yes")){
                condition.append(" and ms.address  is not null ");
            }else{
                condition.append(" and ms.address  is null ");
            }
        }

        if (StringTools.notEmpty(param.get("msaddress"))) {
            String msaddress=param.get("msaddress").toString();
            condition.append(" and ms.address like '%"+msaddress+"%'");
        }

        if (StringTools.notEmpty(param.get("msplantime"))) {
            condition.append(" and ms.starttime  like '%'||:msplantime||'%' ");
            _map.put("msplantime", param.get("msplantime").toString());
        }

        if (StringTools.notEmpty(param.get("msresult"))) {
            condition.append(" and ms.result  = :msresult ");
            _map.put("msresult", param.get("msresult").toString());
        }
        //2019-11-6 新增成绩区间条件筛选
        Object scorearea =param.get("scorearea");
        if (StringTools.notEmpty(param.get("scorearea"))) {
            if (scorearea.toString().equalsIgnoreCase("L60")){
                condition.append(" and to_number(nvl(ms.point,0))  <60 ");
            }else if(scorearea.toString().equalsIgnoreCase("R60")){
                condition.append(" and to_number(nvl(ms.point,0))  >=60 ");
            }

        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));


        queryHQL.append(condition);
        countHQL.append(condition);
        //double count = ((Long) dao.executeSQL(countHQL.toString(), _map)).doubleValue();
        BigDecimal count= (BigDecimal) dao.executeSQLQuery(countHQL.toString(),_map).get(0);
        double pageCount = Math.ceil(count.doubleValue() / (double) perpage);
        //queryHQL.append(" order  by to_number(substr(dur.code, 6, 5)) desc");
        queryHQL.append(" order by dur.name asc ");
        List<Map<String, Object>> list = dao.executeSQLQueryMapPage(queryHQL.toString(), _map, perpage * (page - 1), perpage);
        map.put("count", count.doubleValue());
        map.put("pageCount", pageCount);
        map.put("list", list);
        return map;
    }



    @Override
    public Map<String, Object> getBSResumesMap(int page, int perpage, Map<String, Object> param, DUserResume resume) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer(" select dur.*,cardnototal.cardcount,dr.content as mcontent,dr.id as mid,hu.logintime ,bs.id bsid,bs.point bspoint,bs.result bsresult,bs.address bsaddress,bs.starttime bsstarttime,bs.ticketnum bsticketnum,bs.seatnum bsseatnum,bs.remark bsremark,bs.op6 bsop6,(case bs.notice  when 0 then '未发通知' when 1 then '已发通知' end) as bsnotice from duserresume dur,(select count(1) cardcount, r.cardno from duserresume r group by r.cardno) cardnototal, (select * from  duserremark  where fid like '%"+resume.getPath().replaceAll("/","")+"%') dr,hr_user hu ,dobs_user bs  where 1=1 and  dur.id = dr.rid(+)  and dur.proid = dr.pid(+) and dur.userid=hu.id and dur.id = bs.userid(+) and dur.cardno = cardnototal.cardno(+)");
        StringBuffer countHQL = new StringBuffer(" select count(*)  from duserresume dur,(select count(1) cardcount, r.cardno from duserresume r group by r.cardno) cardnototal, (select * from  duserremark  where fid like '%"+resume.getPath().replaceAll("/","")+"%') dr,hr_user hu ,dobs_user bs  where 1=1 and  dur.id = dr.rid(+)  and dur.proid = dr.pid(+) and dur.userid=hu.id and dur.id = bs.userid(+) and dur.cardno = cardnototal.cardno(+)");
        StringBuffer condition = new StringBuffer();

        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");

        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto");
        }

        Object usercontent = param.get("usercontent");
        if (StringTools.notEmpty(usercontent)){
            if (usercontent.toString().equalsIgnoreCase("已记录")){
                condition.append(" and  dr.content is not null ");
            }else  if (usercontent.toString().equalsIgnoreCase("未记录")){
                condition.append(" and  dr.content is  null ");
            }

        }

        Object ischangejob = param.get("ischangejob");
        if (StringTools.notEmpty(ischangejob)){
            if (ischangejob.toString().equalsIgnoreCase("是")){
                condition.append(" and  dur.njname is not null ");
            }else  if (ischangejob.toString().equalsIgnoreCase("否")){
                condition.append(" and  dur.njname is  null ");
            }

        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));
        condition.append(" order by dur.name asc ");

        queryHQL.append(condition);
        countHQL.append(condition);
        //double count = ((Long) dao.executeSQL(countHQL.toString(), _map)).doubleValue();
        BigDecimal count= (BigDecimal) dao.executeSQLQuery(countHQL.toString(),_map).get(0);
        double pageCount = Math.ceil(count.doubleValue() / (double) perpage);

        List<Map<String, Object>> list = dao.executeSQLQueryMapPage(queryHQL.toString(), _map, perpage * (page - 1), perpage);
        map.put("count", count.doubleValue());
        map.put("pageCount", pageCount);
        map.put("list", list);
        return map;
    }


    @Override
    public Map<String, Object> getOPResumesMap(int page, int perpage, Map<String, Object> param, DUserResume resume) throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer(" select dur.*,cardnototal.cardcount, dr.content as mcontent,dr.fid as mfid ,hu.logintime,  op.id opid,op.address opaddress,op.cont opcont,op.starttime opstarttime,op.point oppoint,op.result opresult,op.bz opremark,(case op.notice  when 0 then '未发通知'when 1 then '已发通知'  end) as opnotice,op.op8 op8 from duserresume dur,(select count(1) cardcount, r.cardno from duserresume r group by r.cardno) cardnototal, (select * from  duserremark  where fid like '%"+resume.getPath().replaceAll("/","")+"%') dr,hr_user hu,dooperation_user op,  where 1=1 and  dur.id = dr.rid(+)  and dur.proid = dr.pid(+) and dur.userid=hu.id and  dur.id = op.userid(+) and dur.cardno = cardnototal.cardno(+) ");
        StringBuffer countHQL = new StringBuffer("select count(1)  from duserresume dur,(select count(1) cardcount, r.cardno from duserresume r group by r.cardno) cardnototal, (select * from  duserremark  where fid like '%"+resume.getPath().replaceAll("/","")+"%') dr,hr_user hu,dooperation_user op  where 1=1 and  dur.id = dr.rid(+)  and dur.proid = dr.pid(+) and dur.userid=hu.id and dur.id = op.userid(+) and dur.cardno = cardnototal.cardno(+) ");
        StringBuffer condition = new StringBuffer();

        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object workyearfrom = param.get("workyearfrom");
        Object workyearto = param.get("workyearto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");

        Map<String, Object> _map = new HashMap<String, Object>();

        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("agefrom", sdf.parse(_date));
            condition.append(" and dur.birthday<=:agefrom");
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            _map.put("ageto", sdf.parse(_date));
            condition.append(" and dur.birthday>=:ageto");
        }
        if (StringTools.notEmpty(workyearfrom)) {
            _map.put("workyearfrom", Integer.parseInt(workyearfrom.toString()));
            condition.append(" and dur.workexperience>=:workyearfrom ");
        }
        if (StringTools.notEmpty(workyearto)) {
            _map.put("workyearto", Integer.parseInt(workyearto.toString()));
            condition.append(" and dur.workexperience<=:workyearto ");
        }
        if (StringTools.notEmpty(heightfrom)) {
            _map.put("heightfrom", Integer.parseInt(heightfrom.toString()));
            condition.append(" and dur.height>=:heightfrom ");
        }
        if (StringTools.notEmpty(heightto)) {
            _map.put("heightto", Integer.parseInt(heightto.toString()));
            condition.append(" and dur.height<=:heightto ");
        }

        Object usercontent = param.get("usercontent");
        if (StringTools.notEmpty(usercontent)){
            if (usercontent.toString().equalsIgnoreCase("已记录")){
                condition.append(" and  dr.content is not null ");
            }else  if (usercontent.toString().equalsIgnoreCase("未记录")){
                condition.append(" and  dr.content is  null ");
            }
        }

        Object ischangejob = param.get("ischangejob");
        if (StringTools.notEmpty(ischangejob)){
            if (ischangejob.toString().equalsIgnoreCase("是")){
                condition.append(" and  dur.njname is not null ");
            }else  if (ischangejob.toString().equalsIgnoreCase("否")){
                condition.append(" and  dur.njname is  null ");
            }
        }

        condition.append(BeanTools.hqlWhere(resume, "dur"));
        condition.append(" order by dur.name asc ");

        queryHQL.append(condition);
        countHQL.append(condition);
        //double count = ((Long) dao.executeSQL(countHQL.toString(), _map)).doubleValue();
        BigDecimal count= (BigDecimal) dao.executeSQLQuery(countHQL.toString(),_map).get(0);
        double pageCount = Math.ceil(count.doubleValue() / (double) perpage);

        List<Map<String, Object>> list = dao.executeSQLQueryMapPage(queryHQL.toString(), _map, perpage * (page - 1), perpage);
        map.put("count", count.doubleValue());
        map.put("pageCount", pageCount);
        map.put("list", list);
        return map;
    }

    @Override
    public List<DUserResume> getDUserResumeByUserid(String[] ids) throws Exception {
        return dao.find("from DUserResume where userid in(:idlist)", ids);
    }

    @Override
    public DUserResume getDUserResumeByUserid(String pid,String uid) throws Exception {
        return  (DUserResume) dao.findUnique("from DUserResume where proid ='" +pid+"' and userid ='"+uid+"'");
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<DUserResume> getDUserResumes(String pid, String[] ids) throws Exception {
        return dao.find("from DUserResume where proid='" + pid + "' and id in(:idlist)", ids);
    }

    @Override
    public List<Map<String,Object>> getRecommendHistory(String cardNo) throws Exception {
        String sql=
                "select r.cardno,\n" +
                        "       r.name,\n" +
                        "       r.jname      oldjob,\n" +
                        "       r.njname      nowjob,\n" +
                        "       r.rfid oldrfid,\n" +
                        "       r.orid       oldrid,\n" +
                        "       r.nrid       oldnrid,\n" +
                        "       r.njname     oldnjname,\n" +
                        "       r.changedate oldchangedate,\n" +
                        "       d.rfid newrfid,\n" +
                        "       d.orid neworid,\n" +
                        "       d.nrid newnrid,\n" +
                        "       d.njname newnjname,\n" +
                        "       d.changedate newchangedate,\n" +
                        "       f.nickname   rflowname,\n" +
                        "       p.pname, r.recommenddate \n" +
                        "  from duserrecommend r, duserresume d, project_info_flow f, project_info p\n" +
                        "  where r.duserresumeid = d.nrid(+)\n" +
                        "   and r.rfid = f.id\n" +
                        "   and r.proid = p.id\n" +
                        "   and r.cardno = :cardno";

        Map params=new HashMap();
        params.put("cardno",cardNo);
        return dao.executeSQLQueryToMap(sql,params);
    }

    @Override
    public DoSSUser getSsUser(String fid, String uid) throws Exception {
        return (DoSSUser) dao.findUnique("from DoSSUser where fid='" + fid + "' and userid='" + uid + "'");
    }

    @Override
    public List<DoSSUser> getSsUserByUserID(String[] ids) throws Exception {
        return dao.find("from DoSSUser where userid in(:idlist)", ids);
    }
}
